﻿Imports System.Data
Imports System.Data.OleDb
Imports geraLaudosAutomatico.conexao
Imports System.Text
Public Class regionalDAO

    Private sql As New StringBuilder
    Private objConn As New conexao
    Private i As Integer
    Private departamento As New departamentoBO
    Private secao As New secaoBO
    Dim regional As New regionalVO





    Public Function getRegionais() As Generic.List(Of regionalVO)

        sql.Clear()

        With sql
            .Append("SELECT *FROM REGIONAL WHERE STATUS=TRUE ORDER BY NUMERO_REGIONAL ")
        End With

        Dim oReader As OleDbDataReader
        Dim lista As New Generic.List(Of regionalVO)



        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                regional = New regionalVO
                regional.ID_REGIONAL = oReader("ID_REGIONAL")
                regional.NUMERO_REGIONAL = oReader("NUMERO_REGIONAL")
                regional.CIDADE_REGIONAL = oReader("CIDADE_REGIONAL")
                regional.DEPARTAMENTO = departamento.getDepartamento(oReader("ID_DEPARTAMENTO"))
                regional.STATUS = oReader("STATUS")
                lista.Add(regional)


            End While

            Return lista

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function



    Public Function getRegional(ByVal id_regional As Integer) As regionalVO

        sql.Clear()
        sql.Append("SELECT * FROM REGIONAL WHERE ID_REGIONAL=" & id_regional)

        Dim oReader As OleDbDataReader

        Dim regional As New regionalVO


        Try
            Dim oComando As New OleDbCommand(SQL.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read

                  regional = New regionalVO
                regional.ID_REGIONAL = oReader("ID_REGIONAL")
                regional.NUMERO_REGIONAL = oReader("NUMERO_REGIONAL")
                regional.CIDADE_REGIONAL = oReader("CIDADE_REGIONAL")
                regional.DEPARTAMENTO = departamento.getDepartamento(oReader("ID_DEPARTAMENTO"))
                regional.STATUS = oReader("STATUS")

            End While

            Return regional

        Catch ex As Exception

            Throw ex

        Finally

            objConn.fechaConexao()

        End Try

    End Function


    Public Function getCidadeRegional() As String

        sql.Clear()
        sql.Append("SELECT *FROM regional INNER JOIN municipios ON regional.CIDADE_REGIONAL = municipios.id_MUNICIPIO where REGIONAL.STATUS=TRUE")
        Dim oReader As OleDbDataReader
        Dim cidadeRegional As String = Nothing
        Try
            Dim oComando As New OleDbCommand(sql.ToString, objConn.abreConexao)

            oReader = oComando.ExecuteReader

            While oReader.Read
                cidadeRegional = IIf(Not IsDBNull(oReader("DESC_MUNICIPIO")), oReader("DESC_MUNICIPIO"), "Informe a cidade")

                If CType(oReader("CIDADE_REGIONAL"), Integer) = 762 Then
                    Exit While
                End If

            End While
            Return cidadeRegional

        Catch ex As Exception
            Throw ex
        Finally
            objConn.fechaConexao()
        End Try
    End Function
End Class
